﻿using System.Data;
using Microsoft.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;

// 读取配置文件
var connectionString = ConfigurationManager.ConnectionStrings["localString"].ConnectionString;

using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
#region 登录
//using SqlCommand cmd = new("select count(*) from Users where Account=@account and Pwd = @pwd", conn);

//SqlParameter parameter1 = new SqlParameter();
//parameter1.ParameterName = "account";
//parameter1.Size = 20;
//parameter1.SqlDbType = SqlDbType.NVarChar;
//parameter1.Value = "zhangsan";

//SqlParameter parameter2 = new SqlParameter();
//parameter2.ParameterName = "pwd";
//parameter2.Size = 20;
//parameter2.SqlDbType = SqlDbType.NVarChar;
//parameter2.Value = "123";

//cmd.Parameters.Add(parameter1);
//cmd.Parameters.Add(parameter2);

//var count = Convert.ToInt32(cmd.ExecuteScalar());
//if (count > 0)
//{
//    Console.WriteLine("登录成功");
//}
//else
//{
//    Console.WriteLine("登录失败，账号或者密码不正确");
//} 
#endregion

#region 添加
//using SqlCommand cmd = new("insert into Users values(@nickName,@account,@pwd)", conn);
//SqlParameter[] parameters =
//{
//    new SqlParameter("nickName","任我行"),
//    new SqlParameter("account","renwoxing"),
//    new SqlParameter("pwd","123"),
//};

//// 批量添加参数对象，传递数组
//cmd.Parameters.AddRange(parameters);

//int result = cmd.ExecuteNonQuery();
//if(result > 0)
//{
//    Console.WriteLine("添加成功");
//}
//else
//{
//    Console.WriteLine("添加失败");
//} 
#endregion

#region 修改
//using SqlCommand cmd = new SqlCommand("update Users set Account=@account,Nickname=@nickName,Pwd=@pwd where Id=@id", conn);

//SqlParameter[] parameters =
//{
//    new ("account",SqlDbType.NVarChar,20),
//    new ("nickName",SqlDbType.NVarChar,20),
//    new ("pwd",SqlDbType.NVarChar,20),
//    new ("id",SqlDbType.Int,4)
//};

//parameters[0].Value = "光泽";
//parameters[1].Value = "Gavin";
//parameters[2].Value = "wgz111420";
//parameters[3].Value = 3;

//cmd.Parameters.AddRange(parameters);
//cmd.ExecuteNonQuery();

#endregion

#region 查询
//SqlDataAdapter adapter = new SqlDataAdapter(
//    "select * from Users where NickName like @nickname and Account like @account",
//    new SqlConnection(connectionString));

//SqlParameter[] parameters =
//{
//    new("nickname",SqlDbType.VarChar),
//    new("account",SqlDbType.VarChar),
//};

//Console.WriteLine("请输入您要查询的姓名:");
//string nickName = Console.ReadLine();
//parameters[0].Value = $"%{nickName}%";
//parameters[1].Value = "%光%";

//adapter.SelectCommand.Parameters.AddRange(parameters);

//DataTable dataTable = new DataTable();
//adapter.Fill(dataTable);

//Console.WriteLine(dataTable.Rows.Count);

#endregion

#region 删除
//using SqlCommand cmd = new("delete from Users where Id in (@ids)",conn);
////SqlParameter sqlParameter = new SqlParameter("id",1);
////cmd.Parameters.Add(sqlParameter);

//SqlParameter parameter = new("ids", SqlDbType.VarChar, 50);
//parameter.Value = "2,3";


//cmd.ExecuteNonQuery();

//using SqlCommand cmd = new("delete from Users where Id in (@id)", conn);
//int[] arr = {2,3};
//SqlParameter parameter = new("id", SqlDbType.Int, 4);
//foreach (int i in arr)
//{
//    parameter.Value = i;
//    cmd.Parameters.Add(parameter);
//    cmd.ExecuteNonQuery();
//    Console.WriteLine("删除了一次");
//    cmd.Parameters.Remove(parameter);
//}

#endregion
